package com.zmh.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.alibaba.fastjson.JSON;
import com.zmh.common.DateUtil;
import com.zmh.common.OperationUtil;
import com.zmh.model.book.BookInfo;

/** 
 * 书籍操作
 * @author zhoumh
 * @version 创建时间：2017年7月28日 下午4:46:48 
 * @Description 
*/
@Repository
public class BookDao {
	 /**
	  * 日志记录  
	  */
   private static final Logger LOGGER = LoggerFactory.getLogger(BookDao.class);
	@Autowired
	private JdbcTemplate jdbcTemplate;
	/**
	 * 查询全部书籍信息
	 * @author zhoumh
	 * @version 创建时间：2017年8月15日 上午11:35:15
	 */
	public List<BookInfo> queryBookByAll(){
		String sqlstr = "SELECT id, uuid, name, author, cover, detail, url, catalog, bstatus, type, state, createtime, updatetime, description FROM bookinfo";
		List<BookInfo> books = new ArrayList<>();
		try {
			books = jdbcTemplate.query(sqlstr,
					new RowMapper<BookInfo>(){
					@Override
					public BookInfo mapRow(ResultSet rs, int rowNum) throws SQLException {
						return objSet(rs, rowNum);
					}
					});
		} catch (EmptyResultDataAccessException e) {
			e.printStackTrace();
		}
		System.out.println(books.size());
		return books;
	}
	/**
	 * 根据UUID查询书籍信息
	 * @author zhoumh
	 * @version 创建时间：2017年8月17日 下午5:19:45
	 */
	public BookInfo queryBookByUuid(String uuid){
		String sqlstr = "SELECT id, uuid, name, author, cover, detail, url, catalog, bstatus, type, state, createtime, updatetime, description FROM bookinfo WHERE uuid=?";
		BookInfo book = new BookInfo();
		Object[] param = new Object[]{uuid};
		try {
			book = jdbcTemplate.queryForObject(sqlstr, param, new RowMapper<BookInfo>(){
					@Override
					public BookInfo mapRow(ResultSet rs, int rowNum) throws SQLException {
						return objSet(rs, rowNum);
					}
					});
		} catch (EmptyResultDataAccessException e) {
			//空返回值异常不做处理
			e.printStackTrace();
		}
		return book;
	}
	/**
	 * 根据URL查询书籍信息
	 * @author zhoumh
	 * @version 创建时间：2017年8月17日 下午5:19:45
	 */
	public BookInfo queryBookByUrl(String url){
		String sqlstr = "SELECT id, uuid, name, author, cover, detail, url, catalog, bstatus, type, state, createtime, updatetime, description FROM bookinfo WHERE url=?";
		BookInfo book = new BookInfo();
		Object[] param = new Object[]{url};
		try {
			book = jdbcTemplate.queryForObject(sqlstr, param, new RowMapper<BookInfo>(){
					@Override
					public BookInfo mapRow(ResultSet rs, int rowNum) throws SQLException {
						return objSet(rs, rowNum);
					}
					});
		} catch (EmptyResultDataAccessException e) {
			//空返回值异常不做处理
			e.printStackTrace();
		}
		return book;
	}
	/**
	 * 根据书籍名称查询书籍信息
	 * @author zhoumh
	 * @version 创建时间：2017年10月16日 下午4:05:32
	 */
	public BookInfo queryBookByName(String name){
		String sqlstr = "SELECT id, uuid, name, author, cover, detail, url, catalog, bstatus, type, state, createtime, updatetime, description FROM bookinfo WHERE name=?";
		BookInfo book = new BookInfo();
		Object[] param = new Object[]{name};
		try {
			book = jdbcTemplate.queryForObject(sqlstr, param, new RowMapper<BookInfo>(){
					@Override
					public BookInfo mapRow(ResultSet rs, int rowNum) throws SQLException {
						return objSet(rs, rowNum);
					}
					});
		} catch (EmptyResultDataAccessException e) {
			//空返回值异常不做处理
			book = null;
			LOGGER.error(name+" 查询结果为空: "+e.getMessage());
		}
		return book;
	}
	/**
	 * 根据书籍状态查询书籍信息
	 * @param bstatus 0 连载，1 完本，2 停更
	 * @return: List<BookInfo> 
	 * @author: zhoumh
	 * @version: 创建时间：2017年11月16日 下午2:24:44 
	 * @Description:
	 */
	public List<BookInfo> selBooksByStatus(String bstatus){
		String sqlstr = "SELECT id, uuid, name, author, cover, detail, url, catalog, bstatus, type, state, createtime, updatetime,description FROM bookinfo WHERE bstatus=?";
		List<BookInfo> books = new ArrayList<>();
		Object[] param = new Object[]{bstatus};
		try {
			books = jdbcTemplate.query(sqlstr, param, new RowMapper<BookInfo>(){
					@Override
					public BookInfo mapRow(ResultSet rs, int rowNum) throws SQLException {
						return objSet(rs, rowNum);
					}
					});
		} catch (EmptyResultDataAccessException e) {
			//空返回值异常不做处理
			books = null;
			LOGGER.error("书籍状态 "+bstatus+" 查询结果为空: "+e.getMessage());
		}
		return books;
	}
	/**
	 * 添加书籍
	 * @author zhoumh
	 * @version 创建时间：2017年9月20日 下午3:03:55
	 */
	public int insertBook(BookInfo book){
		String sqlstr = "insert into bookinfo (uuid, author, name, cover, detail, url, catalog, type, description) values(?,?,?,?,?,?,?,?,?)";
		Object[] param = new Object[]{OperationUtil.getUUID(),book.getAuthor(),book.getName(),book.getCover(),book.getDetail(),book.getUrl(),book.getCatalog(),book.getType(),book.getDescription()};
		int result = jdbcTemplate.update(sqlstr, param);
		return result;
		
	}
	/**
	 * 修改书籍
	 * @author zhoumh
	 * @version 创建时间：2017年9月20日 下午3:04:04
	 */
	public int updateBook(BookInfo book){
		String sqlstr = "update bookinfo set name=?, author=?, cover=?, detail=?, url=?, catalog=?, bstatus=?, type=?, state=?, updatetime=?, description=? where uuid=?";
		Object[] param = new Object[]{book.getName(),book.getAuthor(),book.getCover(),book.getDetail(),book.getUrl(),book.getCatalog(),book.getBstatus(),book.getType(),book.getState(),DateUtil.getDateTime(),book.getDescription(),book.getUuid()};
		int result = jdbcTemplate.update(sqlstr, param);
		return result;
	}
	/**
	 * 删除书籍
	 * @author zhoumh
	 * @version 创建时间：2017年9月20日 下午3:04:17
	 */
	public int deleteBookByUuid(String uuid){
		String sqlstr = "delete from bookinfo where uuid = ?";
		Object[] param = new Object[]{uuid};
		int result = jdbcTemplate.update(sqlstr, param);
		return result;
	}
	/**
	 * 数据库查询结果映射
	 * @author zhoumh
	 * @version 创建时间：2017年8月15日 上午11:36:14
	 */
	private BookInfo objSet(ResultSet rs, int rowNum) throws SQLException {
		BookInfo book = new BookInfo();
		book.setId(rs.getInt("id"));
		book.setUuid(rs.getString("uuid"));
		book.setName(rs.getString("name"));
		book.setAuthor(rs.getString("author"));
		book.setCover(rs.getString("cover"));
		book.setDetail(rs.getString("detail"));
		book.setUrl(rs.getString("url"));
		book.setCatalog(rs.getString("catalog"));
		book.setBstatus(rs.getString("bstatus"));
		book.setType(rs.getString("type"));
		book.setState(rs.getString("state"));
		book.setCreateTime(rs.getString("createtime"));
		book.setUpdateTime(rs.getString("updatetime"));
		book.setDescription(rs.getString("description"));
		return book;
	}
}
